#!/usr/bin/env bash

base_path=$(dirname $0)
dt15=`date -d "15 days ago "  "+%Y-%m-%d"`
#echo ${base_path}

mysql -ureadonly -preadonly123 -h192.168.0.49<<EOF
use jiayou;
select
      from_unixtime(a.updated,'%Y-%m-%d') day,
      case when a.use_shop_id='null' then out_shop_id else a.use_shop_id end use_shop_id,
      case when (oil_type is null or oil_type='') then '非油' else oil_type end productcode,
      sum(case when b.actual_amoun is null then 0 else b.actual_amoun end) direct_discount
from (SELECT * from coupon_info
      where
      from_unixtime(updated,'%Y-%m-%d')>='$dt15')
      as a
      left join
            (SELECT out_shop_id,oil_type,out_trade_no from coupon_oil_order
              where
             from_unixtime(updated,'%Y-%m-%d')>='$dt15') as tmp
      on a.use_trade_no =tmp.out_trade_no
      join (
      SELECT * from coupon_info_ext
       where
      from_unixtime(updated,'%Y-%m-%d')>='$dt15' )
as b
on a.id = b.coupon_id
where coupon_statue = 3 and from_unixtime(a.updated,'%Y-%m-%d')>='$dt15'
and (a.enterprise_id = 5 or a.enterprise_id = 6 )  and (b.enterprise_id = 5 or b.enterprise_id = 6 ) and b.statue=3
and a.use_shop_id != "000001" and a.use_shop_id != "90001" and a.use_shop_id != "000001_VORG" and a.use_shop_id != "90002" and a.use_shop_id != "90003"
group by case when (oil_type is null or oil_type='') then '非油' else oil_type end ,
case when a.use_shop_id='null' then out_shop_id else a.use_shop_id end ,
a.use_shop_id,from_unixtime(a.updated,'%Y-%m-%d');
EOF
